﻿using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using Microsoft.SqlServer.Management.Smo;
using SS.Common.DBBuilder.DBDataStructure;
using SS.Common.DBBuilder.DBInteraction;

namespace SS.Common.DBBuilder
{
    /// <summary>
    /// The class implement AbstractDataContext that is used to create DB structure of SQL Server 
    /// </summary>
    public class SQLServerDataContext: AbstractDataContext
    {
        #region Ctors

        /// <summary>
        /// Initialize instance of AdminDataContext with datasource and database name as inputs
        /// </summary>
        public SQLServerDataContext(string dataSource, string dataBaseName, string userName, string password)
        {
            this._provider = new SQLServerDataProvider(dataSource, dataBaseName, userName, password);
        }

        #endregion

        #region Help methods

        /// <summary>
        /// Update foreign keys and primary keys for all tables of database
        /// </summary>
        /// <param name="DB"></param>
        protected override void SetKeys(DataBase DB)
        {
            Logger.Info("SetKeys --> Start");

            foreach(DBDataStructure.Table table in DB.Tables)
            {
                Logger.InfoFormat("Set keys for table {0} --> Start", table.Name);

                // Build a "serverConnection" with the information of the "sqlConnection"
                var serverConnection =
                    new Microsoft.SqlServer.Management.Common.ServerConnection((SqlConnection)_provider.ObjConnection);

                // The "serverConnection is used in the ctor of the Server.
                var server = new Server(serverConnection);
                var db = server.Databases[_provider.DataBaseName];

                // Get keys list of corresponding table
                var smoTbl = db.Tables[table.Name];
                var fks = this.GetFKs(smoTbl);
                var pks = this.GetPKs(smoTbl);

                foreach(Attribute attr in table.Attributes)
                {
                    var referTo = string.Empty;
                    var isIdentify = false;

                    if (this.IsPK(pks, attr.Name, ref isIdentify))
                    {
                        attr.IsPrimaryKey = true;
                        attr.IsIdentify = isIdentify;
                    }

                    if (this.IsFK(fks, attr.Name, ref referTo))
                    {
                        attr.IsForeignKey = true;
                        attr.ReferTo = referTo;
                    }
                    
                    Logger.InfoFormat("Attribute: {0} => IsPrimaryKey: {1}, IsIdentify: {2}, IsForeignKey: {3}, ReferTo: {4}",
                        new object[] { attr.Name, attr.IsPrimaryKey, attr.IsIdentify, attr.IsForeignKey, attr.ReferTo });
                }

                Logger.InfoFormat("Set keys for table {0} <-- End", table.Name);
            }

            Logger.Info("SetKeys <-- End");
        }

        /// <summary>
        /// Get list of primary keys of specified table
        /// </summary>
        /// <param name="smoTbl"></param>
        /// <returns></returns>
        private IEnumerable<PrimaryKey> GetPKs(Microsoft.SqlServer.Management.Smo.Table smoTbl)
        {
            var lst = smoTbl.Columns.Cast<Column>().Where(n => n.InPrimaryKey).Select(
                n => n.Identity
                         ? new PrimaryKey {Name = n.Name, IsIdentify = true}
                         : new PrimaryKey {Name = n.Name, IsIdentify = false});
            return lst;
        }

        /// <summary>
        /// Get list of SMO foreibn keys from SMO table
        /// </summary>
        /// <param name="tbl"></param>
        /// <returns></returns>
        private IEnumerable<ForeignKey> GetFKs(Microsoft.SqlServer.Management.Smo.Table tbl)
        {
            return tbl.ForeignKeys.Cast<Microsoft.SqlServer.Management.Smo.ForeignKey>().Select(
                n => new ForeignKey
                {
                    Name = GetFKNames(n),
                    ReferTo = n.ReferencedTable
                });
        }

        /// <summary>
        /// Checking if attribute is foreign key 
        /// </summary>
        /// <param name="fks">List of foreign keys which received before</param>
        /// <param name="attrName">Attribute to check if it is foreign key</param>
        /// <param name="referTo">Table that foreign key refer to</param>
        /// <returns></returns>
        private bool IsFK(IEnumerable<ForeignKey> fks, string attrName, ref string referTo)
        {
            var result = false;
            foreach (ForeignKey fk in fks.Where(fk => fk.Name.Any(key => key.Equals(attrName))))
            {
                referTo = fk.ReferTo;
                result = true;
                break;
            }
            return result;
        }

        /// <summary>
        /// Checking if attribute is primary key 
        /// </summary>
        /// <param name="pks">List of primary keys which received before</param>
        /// <param name="attrName">Attribute to check if it is primary key</param>
        /// <param name="isIdentify">Table that primary key refer to</param>
        /// <returns></returns>
        private bool IsPK(IEnumerable<PrimaryKey> pks, string attrName, ref bool isIdentify)
        {
            var result = false;
            foreach (PrimaryKey pk in pks.Where(pk => pk.Name.Equals(attrName)))
            {
                isIdentify = pk.IsIdentify;
                result = true;
                break;
            }
            return result;
        }

        /// <summary>
        /// Retrieve list of foreign keys from SMO ForeignKey
        /// </summary>
        /// <param name="fk"></param>
        /// <returns>List of foreign keys</returns>
        private IEnumerable<string> GetFKNames(Microsoft.SqlServer.Management.Smo.ForeignKey fk)
        {
            return fk.Columns.Cast<ForeignKeyColumn>().Select(n => n.Name);
        }

        #endregion

        #region PrimaryKey and ForeignKey

        /// <summary>
        /// Present an attribute as foreign key of a table
        /// </summary>
        class ForeignKey
        {
            /// <summary>
            /// Multiple attribute are as role of foreign keys
            /// </summary>
            public IEnumerable<string> Name { get; set; }

            /// <summary>
            /// Table which foreign keys refer to
            /// </summary>
            public string ReferTo { get; set; }
        }

        /// <summary>
        /// Present an attribute as primary key of a table
        /// </summary>
        class PrimaryKey
        {
            /// <summary>
            /// Name of primary keys
            /// </summary>
            public string Name { get; set; }

            /// <summary>
            /// If Primary key is identify
            /// </summary>
            public bool IsIdentify { get; set; }

            /// <summary>
            /// The default constructor
            /// </summary>
            public PrimaryKey()
            {
                IsIdentify = false;
            }
        }

        #endregion
    }
}
